--    Program: 		CAPARIMP_RECEIVABLE_ACTIVITY.sql
--    Description:  Import AR Receivable Activity information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_receivables_trx_id		number;
                      		                 		
   cursor cur_rec_act is
      select * from CRST_AR_RECEIVABLES_TRX_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_rec_act loop  
   	 l_err_msg_tmp := '';
   	 
   	 select AR_RECEIVABLES_TRX_S.NEXTVAL
   	 into   l_receivables_trx_id
   	 from   dual;
   	 
   	 update CRST_AR_RECEIVABLES_TRX_ALL
   	 set    receivables_trx_id_n = l_receivables_trx_id,
   	        org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
   	 where  receivables_trx_id = cur.RECEIVABLES_TRX_ID
   	 and    org_id = cur.ORG_ID;       
   	 
   	 begin 
   	   -- import data
       insert into AR_RECEIVABLES_TRX_ALL
       (  
          RECEIVABLES_TRX_ID              ,
          LAST_UPDATED_BY                 ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATE_LOGIN               ,
          CREATION_DATE                   ,
          CREATED_BY                      ,
          NAME                            ,
          CODE_COMBINATION_ID             ,
          SET_OF_BOOKS_ID                 ,
          DESCRIPTION                     ,
          STATUS                          ,
          TYPE                            ,
          START_DATE_ACTIVE               ,
          END_DATE_ACTIVE                 ,
          DEFAULT_ACCTG_DISTRIBUTION_SET  ,
          INACTIVE_DATE                   ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          ORG_ID                          ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          GLOBAL_ATTRIBUTE20              ,
          ASSET_TAX_CODE                  ,
          LIABILITY_TAX_CODE              ,
          GL_ACCOUNT_SOURCE               ,
          TAX_CODE_SOURCE                 ,
          TAX_RECOVERABLE_FLAG            ,
          ACCOUNTING_AFFECT_FLAG          ,
          RISK_ELIMINATION_DAYS
       )
       values
       (
          l_receivables_trx_id                ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
          cur.LAST_UPDATE_DATE                ,
          cur.LAST_UPDATE_LOGIN               ,
          cur.CREATION_DATE                   ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.NAME                            ,
          CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID),
          CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
          cur.DESCRIPTION                     ,
          cur.STATUS                          ,
          cur.TYPE                            ,
          cur.START_DATE_ACTIVE               ,
          cur.END_DATE_ACTIVE                 ,
          cur.DEFAULT_ACCTG_DISTRIBUTION_SET  ,
          cur.INACTIVE_DATE                   ,
          cur.ATTRIBUTE_CATEGORY              ,
          cur.ATTRIBUTE1                      ,
          cur.ATTRIBUTE2                      ,
          cur.ATTRIBUTE3                      ,
          cur.ATTRIBUTE4                      ,
          cur.ATTRIBUTE5                      ,
          cur.ATTRIBUTE6                      ,
          cur.ATTRIBUTE7                      ,
          cur.ATTRIBUTE8                      ,
          cur.ATTRIBUTE9                      ,
          cur.ATTRIBUTE10                     ,
          cur.ATTRIBUTE11                     ,
          cur.ATTRIBUTE12                     ,
          cur.ATTRIBUTE13                     ,
          cur.ATTRIBUTE14                     ,
          cur.ATTRIBUTE15                     ,
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
          cur.GLOBAL_ATTRIBUTE1               ,
          cur.GLOBAL_ATTRIBUTE2               ,
          cur.GLOBAL_ATTRIBUTE3               ,
          cur.GLOBAL_ATTRIBUTE4               ,
          cur.GLOBAL_ATTRIBUTE5               ,
          cur.GLOBAL_ATTRIBUTE6               ,
          cur.GLOBAL_ATTRIBUTE7               ,
          cur.GLOBAL_ATTRIBUTE8               ,
          cur.GLOBAL_ATTRIBUTE9               ,
          cur.GLOBAL_ATTRIBUTE10              ,
          cur.GLOBAL_ATTRIBUTE11              ,
          cur.GLOBAL_ATTRIBUTE12              ,
          cur.GLOBAL_ATTRIBUTE13              ,
          cur.GLOBAL_ATTRIBUTE14              ,
          cur.GLOBAL_ATTRIBUTE15              ,
          cur.GLOBAL_ATTRIBUTE16              ,
          cur.GLOBAL_ATTRIBUTE17              ,
          cur.GLOBAL_ATTRIBUTE18              ,
          cur.GLOBAL_ATTRIBUTE19              ,
          cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
          cur.GLOBAL_ATTRIBUTE20              ,
          cur.ASSET_TAX_CODE                  ,
          cur.LIABILITY_TAX_CODE              ,
          cur.GL_ACCOUNT_SOURCE               ,
          cur.TAX_CODE_SOURCE                 ,
          cur.TAX_RECOVERABLE_FLAG            ,
          cur.ACCOUNTING_AFFECT_FLAG          ,
          cur.RISK_ELIMINATION_DAYS
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_RECEIVABLE_ACTIVITY', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit